package com.creawor.hz_market.t_basestation;


    //import vo.*;
    import com.creawor.hz_market.t_basestation.t_basestation;
import com.creawor.km.util.DateUtil;

	import java.io.FileInputStream;
	import java.io.InputStream;
	import java.util.Date;
import java.sql.ResultSet;
	import java.text.SimpleDateFormat;
	import java.util.ArrayList;

	import jxl.Cell;
	import jxl.Sheet;
	import jxl.Workbook;
	import jxl.WorkbookSettings;
	import net.sf.hibernate.HibernateException;
	import net.sf.hibernate.Session;
	import net.sf.hibernate.SessionFactory;
	import net.sf.hibernate.Transaction;
import net.sf.hibernate.cfg.Configuration;

	public class BasestionHandle {

		// private static final String ADVERTISEMENT = "t_advertisement";
		String date = null;

		public void setString(String date) {
			this.date = date;
		}

		public BasestionHandle() {

		}

		private Date StringtoDate(String str) {
			if (str == null || "".equals(str))
				return null;
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			Date date = null;
			try {

//				date = Date.valueOf(str);
			} catch (Exception ex) {
				ex.printStackTrace();
				return StringtoDate("1970-01");
			}
			return date;
		}

		public void insertData(ArrayList al) {
			try {
				// File propertyFile = new File("hibernate.cfg.xml");
				Configuration conf = null;
				// conf = new Configuration().configure(propertyFile);

				SessionFactory sf = new Configuration().configure()
						.buildSessionFactory();
				SessionFactory sf1 = new Configuration().configure()		.buildSessionFactory();
				// SessionFactory sf= conf.buildSessionFactory();

				Session ups = sf.openSession();
				Session sels = sf1.openSession();
				// Session session =HibernateSessionFactory.getSession();
				Transaction tx = ups.beginTransaction();
				java.sql.Connection con=sels.connection();
				for (int i = 0; i < al.size(); i++) {
					try{
						t_basestation vo = (t_basestation) al.get(i);
						String code=vo.getCode();
						if(null==code || "".equals(code.trim()))
							continue;
						Date date=vo.getInsert_day();
						String dateStr=DateUtil.getStr(date, null);
						if(null!=dateStr){
							dateStr=dateStr.substring(0,7);
							String sql="select code from t_basestation mode where mode.code='"+code+"' and  left(convert(varchar(10),mode.insert_day,120),7)='"+dateStr+"'";
							ResultSet rs=con.createStatement().executeQuery(sql);
							if(rs.next()){
								System.out.println("编号为："+code+"的基站在"+dateStr+"份数据已经导入！");
							}else{
								ups.save(vo);
							}
						}
					}catch(Exception e){
						
					}
					tx.commit();
				}
				tx.commit();
				ups.flush();
				// session.close();
			} catch (HibernateException e) {
				e.printStackTrace();
			}

		}

		public ArrayList readExcel(InputStream is) {
			ArrayList al = new ArrayList();
			try {
				WorkbookSettings workbookSettings = new WorkbookSettings();
				workbookSettings.setEncoding("UTF-8"); // �����������
				Workbook rwb = Workbook.getWorkbook(is, workbookSettings);
				Sheet st = rwb.getSheet(0);
				int length = st.getRow(1).length;
				int rownum = st.getRows();
				for (int i = 2; i < rownum; i++) {
					t_basestation ad = new t_basestation();
					Cell[] oldcell = st.getRow(i);
					if (length < oldcell.length)
						length = oldcell.length;
					Cell[] cell = new Cell[length];
					System.arraycopy(oldcell, 0, cell, 0, oldcell.length);

					if (cell[0] != null && cell[0].getContents().length() != 0) {
						ad.setCode(cell[0].getContents());
					}else{
						continue;
					}
						/*else {
					}
						ad.setCode(" ");
					}*/
					if (cell[1] != null && cell[1].getContents().length() != 0) {
						ad.setName(cell[1].getContents());
					} else {
						ad.setName(" ");
					}
					if (cell[2] != null && cell[2].getContents().length() != 0) {
						ad.setBasetype(cell[2].getContents());
					} else {
						ad.setBasetype(" ");
					}
					if (cell[3] != null && cell[3].getContents().length() != 0) {
						ad.setCompany(cell[3]
								.getContents());
					} /*else {
						ad.setCompany(" ");
					}*/
					
					/*if (cell[1] != null && cell[1].getContents().length() != 0) {
						ad.setUpdated_day(StringtoDate(cell[1].getContents()));
					}*/
					if (cell[4]!=null&&cell[4].getContents().length() != 0) {
//						 vo.setAdvertisement_type(cell[2].getContents());
						String dateStr=cell[4].getContents();
						if(null==dateStr || "".equals(dateStr))
							dateStr=this.date;
						ad.setInsert_day(DateUtil.parse(dateStr,null));
						
					}
					if (cell[5] != null && cell[5].getContents().length() != 0) {
						ad.setX((cell[5].getContents()));
					} else {
						ad.setX(" ");
					}
					if (cell[6] != null && cell[6].getContents().length() != 0) {
						ad.setY((cell[6].getContents()));
					} else {
						ad.setY(" ");
					}
					
					if (cell[7] != null && cell[7].getContents().length() != 0) {
						ad.setCounty(cell[7].getContents());
					} else {
						ad.setCounty(" ");
					}
					
					if (cell[8] != null && cell[8].getContents().length() != 0) {
						ad.setTown(cell[8].getContents());
					} else {
						ad.setTown(" ");
					}
					if (cell[9] != null && cell[9].getContents().length() != 0) {
						ad.setVillage_xz(cell[9].getContents());
					} /*else {
						ad.setVillage_xz(" ");
					}*/
					if (cell[10] != null && cell[10].getContents().length() != 0) {
						ad.setVillage_zr(cell[10].getContents());
					} /*else {
						ad.setVillage_zr(" ");
					}*/
					if (cell[11] != null && cell[11].getContents().length() != 0) {
						ad.setDay_average((cell[11].getContents()));
					} /*else {
						ad.setDay_average(0);
					}*/
					
					if (cell[12] != null && cell[12].getContents().length() != 0) {
						ad.setDay_busy_average((cell[12].getContents()));
					} /*else {
						ad.setDay_busy_average(0);
					}*/
					if (cell[13] != null && cell[13].getContents().length() != 0) {
						ad.setDay_average_portfolio((cell[13].getContents()));
					} /*else {
						ad.setDay_average_portfolio(0);
					}*/
					//ad.setUpdated_day(StringtoDate(date));
					al.add(ad);
				}
				// }
				//            
				//            
				rwb.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			return al;
		}

		/**
		 * ��ȡExcel
		 * 
		 * @param filePath
		 */
		public ArrayList readExcel(String filePath) {
			ArrayList al = null;

			try {
				InputStream is = new FileInputStream(filePath);
				al = readExcel(is);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return al;
		}

		// ����
		public static void main(String[] args) {
			try {
				// ��Excel
				BasestionHandle ex = new BasestionHandle();
				System.out.println("starting....");
				ArrayList al = ex.readExcel("E:\\shapan�ļ�\\jizhantj.xls");
				System.out.println(al.size());
				for (int i = 0; i < al.size(); i++) {
					t_basestation vo = (t_basestation) al.get(i);
					System.out.println("VO:::" + vo.getCode());
					System.out.println("VO:::" + vo.getName());
					System.out.println("VO:::" + vo.getBasetype());
					System.out.println("VO:::" + vo.getCompany());
					
					
					System.out.println("VO:::" + vo.getUpdated_day());
					System.out.println("VO:::" + vo.getX());
					System.out.println("VO:::" + vo.getY());
					System.out.println("VO:::" + vo.getCounty());
					System.out.println("VO:::" + vo.getTown());
					System.out.println("VO:::" + vo.getVillage_xz());
					System.out.println("VO:::" + vo.getVillage_zr());
					System.out.println("VO:::" + vo.getDay_average());
					System.out.println("VO:::" + vo.getDay_busy_average());
//					                                   day_busy_average
					System.out.println("VO:::" + vo.getDay_average_portfolio());
				}
				// ex.insertData(al);
				// ���Excel
				// File fileWrite = new File("e:\\abc.xls");
				// fileWrite.createNewFile();
				// OutputStream os = new FileOutputStream(fileWrite);
				// ADHandle.writeExcel(os);
				// �޸�Excel
				// ExcelHandle.modifyExcel(new file(""),new File(""));
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
